use [master]
Go
if not exists (select * from master..sysdatabases where name ='DBPractica')
	create database DBPractica
go
use [DBPractica]
go
if not exists (select * from sys.tables where name = 'Persona' )
BEGIN 
	create table sp_help Persona(
		Id int IDENTITY(1,1),
		Nombre varchar(100) NOT NULL,
		Paterno varchar(100) NOT NULL,
		Materno varchar(100),
		Nacimiento DateTime,
		Sexo bit,
		Foto varbinary(max)
	)
END
if not exists (select * from sys.key_constraints where name = 'PK_Persona' )
	ALTER TABLE Persona ADD CONSTRAINT PK_Persona PRIMARY KEY (Id)
if not exists (select * from syscolumns where name = 'CU' and id= OBJECT_ID('Persona'))
	ALTER TABLE Persona ADD CU int 

ALTER TABLE Persona ALTER COLUMN Paterno varchar(50)

if not exists (select * from sysobjects where name = 'Producto' and type = 'U')
begin
	create table Producto (
		Id int IDENTITY (1,1),
		Nombre varchar(100),
		Precio Decimal (15,2) NOT NULL DEFAULT (0)
	)
end
if not exists (select * from sys.key_constraints where name = 'PK_Producto')
	ALTER TABLE Producto ADD CONSTRAINT PK_Producto PRIMARY KEY (Id)

if not exists (select * from sys.check_constraints where name = 'CK_Producto_Precio')
	ALTER TABLE Producto ADD CONSTRAINT CK_Producto_Precio CHECK( Precio >= 0)

if not exists (select * from sys.tables where name = 'Compra')
begin
	create table Compra (
		Id int IDENTITY (1,1),
		IdPersona INT,
		IdProducto INT,
		Precio Decimal(15,2),
		Cantidad int DEFAULT (0) ,
		Fecha DateTime DEFAULT (CURRENT_TIMESTAMP),
		Total AS Precio * Cantidad ,
		CONSTRAINT FK_Persona_Compra FOREIGN KEY (IdPersona) REFERENCES [dbo].[Persona](Id),
		CONSTRAINT FK_Compra_Producto FOREIGN KEY(IdProducto) REFERENCES Producto(Id)

	)
end 

